<?php

class AdminController extends Zend_Controller_Action
{
	public function indexAction(){
		$this->view->title = 'Administration Page';
	}

	/* report full action */
	public function operatingreportfullAction(){
		$this->view->title = "Full operating report";

		$form = new ReportForm();
		$this->view->form = $form;

		// get registry value
		$registry = Zend_Registry::getInstance();
		$access_levels = $registry->get('access_level_config');
		$paging_config = $registry->get('paging_config');
		$reporting_config = $registry->get('reporting_config');
		$translator = $registry->get('Zend_Translate');

		// create form
		$reportForm = new ReportForm();
		$exportButton = $reportForm->getElement('exportReportForm');

		// remove the salesman and agent selection controls
		$reportForm->removeElement('salesmanReportForm');
		$reportForm->removeElement('agentReportForm');
		$show_flag = true;
		if($this->_request->isPost()){ // get report conditions when user post information
			$formData = $this->_request->getPost();
			if($reportForm->isValid($formData)){
				$startHour = mysql_escape_string($reportForm->getValue('startHourReportForm'));
				$startDay = mysql_escape_string($reportForm->getValue('startDayReportForm'));
				$endHour = mysql_escape_string($reportForm->getValue('endHourReportForm'));
				$endDay = mysql_escape_string($reportForm->getValue('endDayReportForm'));
				$month = mysql_escape_string($reportForm->getValue('monthReportForm'));
				$year = mysql_escape_string($reportForm->getValue('yearReportForm'));
				$page = 1;
				if($reportForm->getValue('exportReportForm') != NULL){
					$show_flag = false;
				}
			}
		}else{ // get report conditions when users naviagate through the pagination control
			$startHour = mysql_escape_string($this->_request->getParam('startHourReportForm'));
			$startDay = mysql_escape_string($this->_request->getParam('startDayReportForm'));
			$endHour = mysql_escape_string($this->_request->getParam('endHourReportForm'));
			$endDay = mysql_escape_string($this->_request->getParam('endDayReportForm'));
			$month = mysql_escape_string($this->_request->getParam('monthReportForm'));
			$year = mysql_escape_string($this->_request->getParam('yearReportForm'));
			// fetch rows to show
			$page = $this->_getParam('page',1);
		}
		$this->view->page = $page;
		$this->view->itemsPerPage = $paging_config->items;
		if(isset($year) && $year > 0){
			$month += 1 ;
			if(strlen($month) == 1) $month = '0' . $month;
			if(strlen($startDay) == 1) $startDay = '0' . $startDay;
			if(strlen($endDay) == 1) $endDay = '0' . $endDay;
			if(strlen($startHour) == 1) $startHour = '0' . $startHour;
			if(strlen($endHour) == 1) $endHour = '0' . $endHour;
			$this->view->params = array(
				'startHourReportForm' => $startHour,
				'startDayReportForm' => $startDay,
				'endHourReportForm' => $endHour,
				'endDayReportForm' => $endDay,
				'monthReportForm' => $month-1,
				'yearReportForm' => $year
			);
			$startDatetime = $year . "-" . $month . "-" . $startDay . " " . $startHour . ":00:00";
			$endDatetime = $year . "-" . $month . "-" . $endDay . " " . $endHour . ":00:00";
			$users = new Users();
			$billing_table = 'vmg_billing_count' . $year . $month;

			$select = $users->getAdapter()->select()
								->from(array( 'bt' => $billing_table))
								->where('bt.date BETWEEN CAST(\'' . $startDatetime . '\' AS DATETIME) AND CAST(\'' . $endDatetime . '\' AS DATETIME)')
								->joinleft(array('su' => 'vmg_service_user'),  '(bt.msisdn = su.msisdn)', array('su.activ_date as active_date', 'su.service_userid as service_userid'))
								->joinleft(array('us' => 'vmg_user_service'),  'su.service_channel_id = us.id', array('us.agent_id as agent_id'))
								->joinleft(array('u' => 'vmg_user'), 'u.id = us.agent_id', array('u.id as agent_id', 'u.name as agent_name', 'u.managed_by'))
								->joinleft(array('um' => 'vmg_user'), 'u.managed_by = um.id', array('um.name as salesman_name', 'um.id as salesman_id'))
								->order('bt.date DESC')
								->where('(su.service_channel_id IS NULL) OR
								((su.service_channel_id IS NOT NULL ) AND (bt.msisdn in (SELECT su.msisdn FROM `vmg_service_user` AS su
	LEFT JOIN `vmg_user_service` AS us ON su.service_channel_id = us.id)) AND (su.activ_date in (SELECT max(activ_date) FROM vmg_service_user WHERE msisdn = bt.msisdn and bt.date > activ_date)))')
								;

			$select_sum = $users->getAdapter()->select()
								->from(array( 'bt' => $billing_table), array('total_money' => 'sum(money)', 'total_mo' => 'sum(mo_count)', 'total_mt' => 'sum(mt_count)', 'total_mp' => 'sum(cdr_count)'))
								->where('bt.date BETWEEN CAST(\'' . $startDatetime . '\' AS DATETIME) AND CAST(\'' . $endDatetime . '\' AS DATETIME)')
								->joinleft(array('su' => 'vmg_service_user'),  '(bt.msisdn = su.msisdn)', array())
								->joinleft(array('us' => 'vmg_user_service'),  'su.service_channel_id = us.id', array())
								->joinleft(array('u' => 'vmg_user'), 'u.id = us.agent_id', array())
								->joinleft(array('um' => 'vmg_user'), 'u.managed_by = um.id', array())
								->order('bt.date DESC')
								->where('(su.service_channel_id IS NULL) OR
								((su.service_channel_id IS NOT NULL ) AND (bt.msisdn in (SELECT su.msisdn FROM `vmg_service_user` AS su
	LEFT JOIN `vmg_user_service` AS us ON su.service_channel_id = us.id)) AND (su.activ_date in (SELECT max(activ_date) FROM vmg_service_user WHERE msisdn = bt.msisdn and bt.date > activ_date)))')
								;

			$select_count = $users->getAdapter()->select()
								->from(array( 'bt' => $billing_table), array('num_rows' => 'count(*)'))
								->where('bt.date BETWEEN CAST(\'' . $startDatetime . '\' AS DATETIME) AND CAST(\'' . $endDatetime . '\' AS DATETIME)')
								->joinleft(array('su' => 'vmg_service_user'),  '(bt.msisdn = su.msisdn)', array())
								->joinleft(array('us' => 'vmg_user_service'),  'su.service_channel_id = us.id', array())
								->joinleft(array('u' => 'vmg_user'), 'u.id = us.agent_id', array())
								->joinleft(array('um' => 'vmg_user'), 'u.managed_by = um.id', array())
								->order('bt.date DESC')
								->where('(su.service_channel_id IS NULL) OR
								((su.service_channel_id IS NOT NULL ) AND (bt.msisdn in (SELECT su.msisdn FROM `vmg_service_user` AS su
	LEFT JOIN `vmg_user_service` AS us ON su.service_channel_id = us.id)) AND (su.activ_date in (SELECT max(activ_date) FROM vmg_service_user WHERE msisdn = bt.msisdn and bt.date > activ_date)))')
								;

			if($show_flag){	// show the result
				try{
					$row = $users->getAdapter()->fetchRow($select_sum);
				}catch(Zend_Db_Statement_Exception $e){
					$exportButton->setValue('Export data');
					$reportForm->getElement('submitReportForm')->setValue("Show report");
					$this->view->form = $reportForm;
					return;
				}
				$total_amount = $row["total_money"];
				$total_mo = $row['total_mo'];
				$total_mt = $row['total_mt'];
				$total_mp = $row['total_mp'];
				$this->view->total_amount = $total_amount;
				$this->view->total_mo = $total_mo;
				$this->view->total_mt = $total_mt;
				$this->view->total_mp = $total_mp;
				// init paginator object
				$paginator = Zend_Paginator::factory($select);
				$paginator->setItemCountPerPage($paging_config->items);
				$paginator->setCurrentPageNumber($page);
				$this->view->paginator=$paginator;
				$reportForm->populate($this->view->params);
			}else{ // export all result to a excel file
				//open file
				$filename = uniqid() . ".csv";
				 header('Content-Type: text/csv;');
                 header("Content-Disposition: attachment; filename=$filename");
                 try{
					$row = $users->getAdapter()->fetchRow($select_count);
                 }catch(Zend_Db_Statement_Exception $e){
					$exportButton->setValue('Export data');
					$reportForm->getElement('submitReportForm')->setValue("Show report");
					$this->view->form = $reportForm;
					return;
				 }
					$num_records = $row["num_rows"];
					$select_limit = $users->getAdapter()->select()
								->from(array( 'bt' => $billing_table))
								->where('bt.date BETWEEN CAST(\'' . $startDatetime . '\' AS DATETIME) AND CAST(\'' . $endDatetime . '\' AS DATETIME)')
								->joinleft(array('su' => 'vmg_service_user'),  '(bt.msisdn = su.msisdn)', array('su.activ_date as active_date', 'su.service_userid as service_userid'))
								->joinleft(array('us' => 'vmg_user_service'),  'su.service_channel_id = us.id', array('us.agent_id as agent_id'))
								->joinleft(array('u' => 'vmg_user'), 'u.id = us.agent_id', array('u.id as agent_id', 'u.name as agent_name', 'u.managed_by'))
								->joinleft(array('um' => 'vmg_user'), 'u.managed_by = um.id', array('um.name as salesman_name', 'um.id as salesman_id'))
								->order('bt.date DESC')
								->where('(su.service_channel_id IS NULL) OR
								((su.service_channel_id IS NOT NULL ) AND (bt.msisdn in (SELECT su.msisdn FROM `vmg_service_user` AS su
	LEFT JOIN `vmg_user_service` AS us ON su.service_channel_id = us.id)) AND (su.activ_date in (SELECT max(activ_date) FROM vmg_service_user WHERE msisdn = bt.msisdn and bt.date > activ_date)))')
								;
					echo "Nam, " . $year;
					echo "\n";
					echo "Thang, " . $month;
					echo "\n";
					$i = $num_records/1000;
				 	$tmp = array(
				 		$translator->_("Ngay"),
				 		$translator->_("Gio"),
				 		$translator->_("Di dong"),
				 		$translator->_("Dai ly"),
				 		$translator->_("Nhan vien quan ly"),
						$translator->_("Dau so"),
						$translator->_("So luong MO"),
						$translator->_("So luong MT"),
						$translator->_("So luong MP"),
						$translator->_("Doanh thu tam tinh")
					);
					echo implode("," , $tmp) . "\n";
					for($j = 0; $j <= $i; $j++){
						$select_limit->limit(1000, $j * 1000);
						$rows = $users->getAdapter()->fetchAll($select_limit);
						foreach($rows as $row){
							$row['msisdn'] = '\'' . str_replace("+84", "0", $row['msisdn']);
							echo substr($row['date'], 8,2) . ",";
							echo substr($row['date'], 11, 2) . ",";
							echo $row['msisdn']. ",";
							echo $row['agent_name']. ",";
							echo $row['salesman_name']. ",";
							echo $row['short_code']. ",";
							echo $row['mo_count']. ",";
							echo $row['mt_count']. ",";
							echo $row['cdr_count']. ",";
							echo $row['money'];
							echo "\n";
						}
					}
					exit;
				}
			}
		// attach form to view
		$exportButton->setValue('Export data');
		$reportForm->getElement('submitReportForm')->setValue("Show report");
		$this->view->form = $reportForm;

	}


	/**
	 * report by salesman action
	 */
	public function operatingreportbysalesmanAction(){

		// get registry value
		$registry = Zend_Registry::getInstance();
		$access_levels = $registry->get('access_level_config');
		$paging_config = $registry->get('paging_config');
		$reporting_config = $registry->get('reporting_config');
		$translator = $registry->get('Zend_Translate');

		//check to see whether the request is an ajax or not
		if(!$this->getRequest()->getParam('ajax')){

			$this->view->title = "Salesman operating report";
			// create form
			$reportForm = new ReportForm();
			$exportButton = $reportForm->getElement('exportReportForm');

			// remove the salesman and agent selection controls
			// $reportForm->removeElement('salesmanReportForm');
			// $reportForm->removeElement('agentReportForm');
			$show_flag = true;
			$page = 1;

			if($this->_request->isPost()){ // get report conditions when user post information
				$formData = $this->_request->getPost();
				$salesman_id = $formData["salesmanReportForm"];
				$agent_id = $formData["agentReportForm"];
				if($salesman_id != null){
					$users = new Users();
					$select = $users->getAdapter()->select()
											->from(array('u' => 'vmg_user'), array('u.id as agent_id', 'u.name as agent_name'))
								            ->joinleft(array('t' => 'vmg_user_type'),
								                        'u.type_id = t.id', array('type_name'))
								            ->where('t.access_level = ' . $access_levels->mobishop_level)
								            ->where('u.managed_by = ?', (int)$salesman_id);
					$rows = $users->getAdapter()->fetchAll($select);
					$reportForm->getElement('agentReportForm')->addMultiOption(-1, $translator->_('All'));
					foreach($rows as $row){
						$reportForm->getElement('agentReportForm')->addMultiOption($row['agent_id'], $row['agent_name']);
					}
					$reportForm->getElement('agentReportForm')->setValue($agent_id);
				}
				if($reportForm->isValid($formData)){
					$startHour = mysql_escape_string($reportForm->getValue('startHourReportForm'));
					$startDay = mysql_escape_string($reportForm->getValue('startDayReportForm'));
					$endHour = mysql_escape_string($reportForm->getValue('endHourReportForm'));
					$endDay = mysql_escape_string($reportForm->getValue('endDayReportForm'));
					$month = mysql_escape_string($reportForm->getValue('monthReportForm'));
					$year = mysql_escape_string($reportForm->getValue('yearReportForm'));
					//$salesman_id = mysql_escape_string($reportForm->getValue('salesmanReportForm'));
					$agent_id = mysql_escape_string($reportForm->getValue('agentReportForm'));
					$page = 1;
					if($reportForm->getValue('exportReportForm') != NULL){
						$show_flag = false;
					}
				}
			}else{ // get report conditions when users naviagate through the pagination control
				$startHour = mysql_escape_string($this->_request->getParam('startHourReportForm'));
				$startDay = mysql_escape_string($this->_request->getParam('startDayReportForm'));
				$endHour = mysql_escape_string($this->_request->getParam('endHourReportForm'));
				$endDay = mysql_escape_string($this->_request->getParam('endDayReportForm'));
				$month = mysql_escape_string($this->_request->getParam('monthReportForm'));
				$year = mysql_escape_string($this->_request->getParam('yearReportForm'));
				$salesman_id = mysql_escape_string($this->_request->getParam('salesmanReportForm'));
				$agent_id = mysql_escape_string($this->_request->getParam('agentReportForm'));
				// fetch rows to show
				$page = $this->_getParam('page',1);
				if($salesman_id != null){
					$users = new Users();
					$select = $users->getAdapter()->select()
											->from(array('u' => 'vmg_user'), array('u.id as agent_id', 'u.name as agent_name'))
								            ->joinleft(array('t' => 'vmg_user_type'),
								                        'u.type_id = t.id', array('type_name'))
								            ->where('t.access_level = ' . $access_levels->mobishop_level)
								            ->where('u.managed_by = ?', (int)$salesman_id);
					$rows = $users->getAdapter()->fetchAll($select);
					$reportForm->getElement('agentReportForm')->addMultiOption(-1, $translator->_('All'));
					foreach($rows as $row){
						$reportForm->getElement('agentReportForm')->addMultiOption($row['agent_id'], $row['agent_name']);
					}
					$reportForm->getElement('agentReportForm')->setValue($agent_id);
				}
			}

			$this->view->page = $page;
			$this->view->itemsPerPage = $paging_config->items;
			if(isset($year) && $year > 0){
				$month += 1 ;
				if(strlen($month) == 1) $month = '0' . $month;
				if(strlen($startDay) == 1) $startDay = '0' . $startDay;
				if(strlen($endDay) == 1) $endDay = '0' . $endDay;
				if(strlen($startHour) == 1) $startHour = '0' . $startHour;
				if(strlen($endHour) == 1) $endHour = '0' . $endHour;
				$this->view->params = array(
					'startHourReportForm' => $startHour,
					'startDayReportForm' => $startDay,
					'endHourReportForm' => $endHour,
					'endDayReportForm' => $endDay,
					'monthReportForm' => $month-1,
					'yearReportForm' => $year,
					'salesmanReportForm' => $salesman_id,
					'agentReportForm' => $agent_id
				);
				$startDatetime = $year . "-" . $month . "-" . $startDay . " " . $startHour . ":00:00";
				$endDatetime = $year . "-" . $month . "-" . $endDay . " " . $endHour . ":00:00";
				$users = new Users();
				$billing_table = 'vmg_billing_count' . $year . $month;
				$select = $users->getAdapter()->select()
									->from(array( 'bt' => $billing_table))
									->where('bt.msisdn in (SELECT su.msisdn FROM `vmg_service_user` AS su
		LEFT JOIN `vmg_user_service` AS us ON su.service_channel_id = us.id)')
									->where('bt.date BETWEEN CAST(\'' . $startDatetime . '\' AS DATETIME) AND CAST(\'' . $endDatetime . '\' AS DATETIME)')
									->joinleft(array('su' => 'vmg_service_user'),  '(bt.msisdn = su.msisdn)', array('su.msisdn', 'su.activ_date as active_date', 'su.service_userid as service_userid'))
									->joinleft(array('us' => 'vmg_user_service'),  'su.service_channel_id = us.id', array('us.agent_id as agent_id'))
									->joinleft(array('u' => 'vmg_user'), 'u.id = us.agent_id', array('u.id', 'u.name as agent_name', 'u.managed_by'))
									->joinleft(array('um' => 'vmg_user'), 'u.managed_by = um.id', array('um.name as salesman_name'))
									->where('u.managed_by = ?', $salesman_id)
									->order('date DESC')
									->where('su.activ_date in (SELECT max(activ_date) FROM vmg_service_user WHERE msisdn = bt.msisdn and bt.date > activ_date)')
									;


				$select_sum = $users->getAdapter()->select()
									->from(array( 'bt' => $billing_table), array( "sum(mo_count) as total_mo", "sum(mt_count) as total_mt", "sum(cdr_count) as total_mp", "sum(money) as total_money"))
									->where('bt.msisdn in (SELECT su.msisdn FROM `vmg_service_user` AS su
		LEFT JOIN `vmg_user_service` AS us ON su.service_channel_id = us.id)')
									->where('bt.date BETWEEN CAST(\'' . $startDatetime . '\' AS DATETIME) AND CAST(\'' . $endDatetime . '\' AS DATETIME)')
									->joinleft(array('su' => 'vmg_service_user'),  '(bt.msisdn = su.msisdn)', array('su.msisdn', 'su.activ_date as active_date', 'su.service_userid as service_userid'))
									->joinleft(array('us' => 'vmg_user_service'),  'su.service_channel_id = us.id', array('us.agent_id as agent_id'))
									->joinleft(array('u' => 'vmg_user'), 'u.id = us.agent_id', array('u.id', 'u.name as agent_name', 'u.managed_by'))
									->joinleft(array('um' => 'vmg_user'), 'u.managed_by = um.id', array('um.name as salesman_name'))
									->group(array('u.managed_by'))
									->where('u.managed_by = ?', $salesman_id)
									->where('su.activ_date in (SELECT max(activ_date) FROM vmg_service_user WHERE msisdn = bt.msisdn and bt.date > activ_date)')
									;

				$select_count = $users->getAdapter()->select()
									->from(array( 'bt' => $billing_table), array( "count(*) as num_rows"))
									->where('bt.msisdn in (SELECT su.msisdn FROM `vmg_service_user` AS su
		LEFT JOIN `vmg_user_service` AS us ON su.service_channel_id = us.id)')
									->where('bt.date BETWEEN CAST(\'' . $startDatetime . '\' AS DATETIME) AND CAST(\'' . $endDatetime . '\' AS DATETIME)')
									->joinleft(array('su' => 'vmg_service_user'),  '(bt.msisdn = su.msisdn)', array('su.msisdn', 'su.activ_date as active_date', 'su.service_userid as service_userid'))
									->joinleft(array('us' => 'vmg_user_service'),  'su.service_channel_id = us.id', array('us.agent_id as agent_id'))
									->joinleft(array('u' => 'vmg_user'), 'u.id = us.agent_id', array('u.id', 'u.name as agent_name', 'u.managed_by'))
									->joinleft(array('um' => 'vmg_user'), 'u.managed_by = um.id', array('um.name as salesman_name'))
									->group(array('u.managed_by'))
									->where('u.managed_by = ?', $salesman_id)
									->where('su.activ_date in (SELECT max(activ_date) FROM vmg_service_user WHERE msisdn = bt.msisdn and bt.date > activ_date)')
									;
				// check whether or not a agent_id is specified
				if($agent_id != -1){
					$select = $select->where('u.id = ?', (int)$agent_id);
					$select_sum = $select_sum->where('u.id = ?', (int)$agent_id);
					$select_count = $select_count->where('u.id = ?', (int)$agent_id);
				}

				if($show_flag){	// show the result
					try{
						$row = $users->getAdapter()->fetchRow($select_sum);
					}catch(Zend_Db_Statement_Exception $e){
						$exportButton->setValue('Export data');
						$reportForm->getElement('submitReportForm')->setValue("Show report");
						$this->view->form = $reportForm;
						return;
					}
					$total_amount = $row["total_money"];
					$total_mo = $row['total_mo'];
					$total_mt = $row['total_mt'];
					$total_mp = $row['total_mp'];
					$this->view->total_amount = $total_amount;
					$this->view->total_mo = $total_mo;
					$this->view->total_mt = $total_mt;
					$this->view->total_mp = $total_mp;
					// init paginator object
					$paginator = Zend_Paginator::factory($select);
					$paginator->setItemCountPerPage($paging_config->items);
					$paginator->setCurrentPageNumber($page);
					$this->view->paginator=$paginator;
					$reportForm->populate($this->view->params);
				}else{ // export all result to a excel file
					//open file
					$filename = uniqid() . ".csv";
					 header('Content-Type: text/csv;');
	                 header("Content-Disposition: attachment; filename=$filename");
	                 try{
						$row = $users->getAdapter()->fetchRow($select_count);
	                 }catch(Zend_Db_Statement_Exception $e){
						$exportButton->setValue('Export data');
						$reportForm->getElement('submitReportForm')->setValue("Show report");
						$this->view->form = $reportForm;
						return;
					 }
					$num_records = $row["num_rows"];
					$select_limit =$select = $users->getAdapter()->select()
								->from(array( 'bt' => $billing_table))
								->where('bt.msisdn in (SELECT su.msisdn FROM `vmg_service_user` AS su
	LEFT JOIN `vmg_user_service` AS us ON su.service_channel_id = us.id)')
								->where('bt.date BETWEEN CAST(\'' . $startDatetime . '\' AS DATETIME) AND CAST(\'' . $endDatetime . '\' AS DATETIME)')
								->joinleft(array('su' => 'vmg_service_user'),  '(bt.msisdn = su.msisdn)', array('su.msisdn', 'su.activ_date as active_date', 'su.service_userid as service_userid'))
								->joinleft(array('us' => 'vmg_user_service'),  'su.service_channel_id = us.id', array('us.agent_id as agent_id'))
								->joinleft(array('u' => 'vmg_user'), 'u.id = us.agent_id', array('u.id', 'u.name as agent_name', 'u.managed_by'))
								->joinleft(array('um' => 'vmg_user'), 'u.managed_by = um.id', array('um.name as salesman_name'))
								->where('u.managed_by = ?', $salesman_id)
								->order(array('salesman_name', 'date'))
								->where('su.activ_date in (SELECT max(activ_date) FROM vmg_service_user WHERE msisdn = bt.msisdn and bt.date > activ_date)')
								;
					// check to see whether we will include all agents
					if($agent_id != -1){
						$select_limit = $select->where('u.id = ?', (int)$agent_id);
					}
						echo "Nam, " . $year;
						echo "\n";
						echo "Thang, " . $month;
						echo "\n";
						$i = $num_records/1000;
					 	$tmp = array(
					 		$translator->_("Ngay"),
					 		$translator->_("Gio"),
					 		$translator->_("Di dong"),
					 		$translator->_("Dai ly"),
					 		$translator->_("Dau so"),
							$translator->_("So luong MO"),
							$translator->_("So luong MT"),
							$translator->_("So luong MP"),
							$translator->_("Doanh thu tam tinh")
						);
						echo implode("," , $tmp) . "\n";
						for($j = 0; $j <= $i; $j++){
							$select_limit->limit(1000, $j * 1000);
							$rows = $users->getAdapter()->fetchAll($select_limit);
							foreach($rows as $row){
								$row['msisdn'] = '\'' . str_replace("+84", "0", $row['msisdn']);
								echo substr($row['date'], 8,2) . ",";
								echo substr($row['date'], 11, 2) . ",";
								echo $row['msisdn']. ",";
								echo $row['agent_name']. ",";
								echo $row['short_code']. ",";
								echo $row['mo_count']. ",";
								echo $row['mt_count']. ",";
								echo $row['cdr_count']. ",";
								echo $row['money'];
								echo "\n";
							}
						}
						exit;
					}
				}
			// attach form to view
			$exportButton->setValue('Export data');
			$reportForm->getElement('submitReportForm')->setValue("Show report");
			$this->view->form = $reportForm;
		}else{ // not a ajax request
			$sid = $_GET['sid'];
			if($sid != ''){
				$users = new Users();
				$select = $users->getAdapter()->select()
											->from(array('u' => 'vmg_user'), array('u.id as agent_id', 'u.name as agent_name'))
								            ->joinleft(array('t' => 'vmg_user_type'),
								                        'u.type_id = t.id', array('type_name'))
								            ->where('t.access_level = ' . $access_levels->mobishop_level)
								            ->where('u.managed_by = ?', (int)$sid);
				$rows = $users->getAdapter()->fetchAll($select);
				if(count($rows)){
					$results = array_merge(array(array('agent_id' => -1, 'agent_name' => $translator->_('All'))),$rows);
				}else{
					$results = array(array('agent_id' => -2, 'agent_name' => ''));
				}
			}
			echo Zend_Json::encode($results);
			exit;

		}
	}


	/**
	 * report by agent action
	 */
	public function operatingreportbyagentAction(){
		$this->view->title = "Agent operating report";

		$form = new ReportForm();
		$this->view->form = $form;

		// get registry value
		$registry = Zend_Registry::getInstance();
		$access_levels = $registry->get('access_level_config');
		$paging_config = $registry->get('paging_config');
		$reporting_config = $registry->get('reporting_config');
		$translator = $registry->get('Zend_Translate');

		// create form
		$reportForm = new ReportForm();
		$exportButton = $reportForm->getElement('exportReportForm');


		// remove the salesman and agent selection controls
		$reportForm->removeElement('salesmanReportForm');
		//$reportForm->removeElement('agentReportForm');
		$show_flag = true;
		if($this->_request->isPost()){ // get report conditions when user post information
			$formData = $this->_request->getPost();
			if($reportForm->isValid($formData)){
				$startHour = mysql_escape_string($reportForm->getValue('startHourReportForm'));
				$startDay = mysql_escape_string($reportForm->getValue('startDayReportForm'));
				$endHour = mysql_escape_string($reportForm->getValue('endHourReportForm'));
				$endDay = mysql_escape_string($reportForm->getValue('endDayReportForm'));
				$month = mysql_escape_string($reportForm->getValue('monthReportForm'));
				$year = mysql_escape_string($reportForm->getValue('yearReportForm'));
				$agent_id = mysql_escape_string($reportForm->getValue('agentReportForm'));
				$page = 1;
				if($reportForm->getValue('exportReportForm') != NULL){
					$show_flag = false;
				}
			}
		}else{ // get report conditions when users naviagate through the pagination control
			$startHour = mysql_escape_string($this->_request->getParam('startHourReportForm'));
			$startDay = mysql_escape_string($this->_request->getParam('startDayReportForm'));
			$endHour = mysql_escape_string($this->_request->getParam('endHourReportForm'));
			$endDay = mysql_escape_string($this->_request->getParam('endDayReportForm'));
			$month = mysql_escape_string($this->_request->getParam('monthReportForm'));
			$year = mysql_escape_string($this->_request->getParam('yearReportForm'));
			$agent_id = mysql_escape_string($this->_request->getParam('agentReportForm'));
			// fetch rows to show
			$page = $this->_getParam('page',1);
		}
		$this->view->page = $page;
		$this->view->itemsPerPage = $paging_config->items;
		if(isset($year) && $year > 0){
			$month += 1 ;
			if(strlen($month) == 1) $month = '0' . $month;
			if(strlen($startDay) == 1) $startDay = '0' . $startDay;
			if(strlen($endDay) == 1) $endDay = '0' . $endDay;
			if(strlen($startHour) == 1) $startHour = '0' . $startHour;
			if(strlen($endHour) == 1) $endHour = '0' . $endHour;
			$this->view->params = array(
				'startHourReportForm' => $startHour,
				'startDayReportForm' => $startDay,
				'endHourReportForm' => $endHour,
				'endDayReportForm' => $endDay,
				'monthReportForm' => $month-1,
				'yearReportForm' => $year,
				'agentReportForm' => $agent_id
			);
			$startDatetime = $year . "-" . $month . "-" . $startDay . " " . $startHour . ":00:00";
			$endDatetime = $year . "-" . $month . "-" . $endDay . " " . $endHour . ":00:00";
			$users = new Users();
			$billing_table = 'vmg_billing_count' . $year . $month;
			$select = $users->getAdapter()->select()
								->from(array( 'bt' => $billing_table))
								->where('bt.msisdn in (SELECT su.msisdn FROM `vmg_service_user` AS su
	LEFT JOIN `vmg_user_service` AS us ON su.service_channel_id = us.id)')
								->where('bt.date BETWEEN CAST(\'' . $startDatetime . '\' AS DATETIME) AND CAST(\'' . $endDatetime . '\' AS DATETIME)')
								->joinleft(array('su' => 'vmg_service_user'),  '(bt.msisdn = su.msisdn)', array('su.msisdn', 'su.activ_date as active_date', 'su.service_userid as service_userid'))
								->joinleft(array('us' => 'vmg_user_service'),  'su.service_channel_id = us.id', array('us.agent_id as agent_id'))
								->joinleft(array('u' => 'vmg_user'), 'u.id = us.agent_id', array('u.id', 'u.name as agent_name', 'u.managed_by'))
								->joinleft(array('um' => 'vmg_user'), 'u.managed_by = um.id', array('um.name as salesman_name'))
								->where('u.id = ?', $agent_id)
								->where('su.activ_date in (SELECT max(activ_date) FROM vmg_service_user WHERE msisdn = bt.msisdn and bt.date > activ_date)')
								->order('bt.date DESC')

								;

			$select_sum = $users->getAdapter()->select()
								->from(array( 'bt' => $billing_table), array( "sum(mo_count) as total_mo", "sum(mt_count) as total_mt", "sum(cdr_count) as total_mp", "sum(money) as total_money"))
								->where('bt.msisdn in (SELECT su.msisdn FROM `vmg_service_user` AS su
	LEFT JOIN `vmg_user_service` AS us ON su.service_channel_id = us.id)')
								->where('bt.date BETWEEN CAST(\'' . $startDatetime . '\' AS DATETIME) AND CAST(\'' . $endDatetime . '\' AS DATETIME)')
								->joinleft(array('su' => 'vmg_service_user'),  '(bt.msisdn = su.msisdn)', array('su.msisdn', 'su.activ_date as active_date', 'su.service_userid as service_userid'))
								->joinleft(array('us' => 'vmg_user_service'),  'su.service_channel_id = us.id', array('us.agent_id as agent_id'))
								->joinleft(array('u' => 'vmg_user'), 'u.id = us.agent_id', array('u.id', 'u.name as agent_name', 'u.managed_by'))
								->joinleft(array('um' => 'vmg_user'), 'u.managed_by = um.id', array('um.name as salesman_name'))
								->where('u.id = ?', $agent_id)
								->group(array('u.id'))
								->where('su.activ_date in (SELECT max(activ_date) FROM vmg_service_user WHERE msisdn = bt.msisdn and bt.date > activ_date)')
								;

			$select_count = $users->getAdapter()->select()
								->from(array( 'bt' => $billing_table), array( "count(*) as num_rows"))
								->where('bt.msisdn in (SELECT su.msisdn FROM `vmg_service_user` AS su
	LEFT JOIN `vmg_user_service` AS us ON su.service_channel_id = us.id)')
								->where('bt.date BETWEEN CAST(\'' . $startDatetime . '\' AS DATETIME) AND CAST(\'' . $endDatetime . '\' AS DATETIME)')
								->joinleft(array('su' => 'vmg_service_user'),  '(bt.msisdn = su.msisdn)', array('su.msisdn', 'su.activ_date as active_date', 'su.service_userid as service_userid'))
								->joinleft(array('us' => 'vmg_user_service'),  'su.service_channel_id = us.id', array('us.agent_id as agent_id'))
								->joinleft(array('u' => 'vmg_user'), 'u.id = us.agent_id', array('u.id', 'u.name as agent_name', 'u.managed_by'))
								->joinleft(array('um' => 'vmg_user'), 'u.managed_by = um.id', array('um.name as salesman_name'))
								->group(array('u.id'))
								->where('u.id = ?', $agent_id)
								->where('su.activ_date in (SELECT max(activ_date) FROM vmg_service_user WHERE msisdn = bt.msisdn and bt.date > activ_date)')
								;
//			foreach($this->view->params as $key => $value){
//				$reportForm->getElement($key)->setValue($value);
//			}

			if($show_flag){	// show the result
				try{
					$row = $users->getAdapter()->fetchRow($select_sum);
				}catch(Zend_Db_Statement_Exception $e){
					$exportButton->setValue('Export data');
					$reportForm->getElement('submitReportForm')->setValue("Show report");
					$this->view->form = $reportForm;
					return;
				}
				$total_amount = $row["total_money"];
				$total_mo = $row['total_mo'];
				$total_mt = $row['total_mt'];
				$total_mp = $row['total_mp'];
				$this->view->total_amount = $total_amount;
				$this->view->total_mo = $total_mo;
				$this->view->total_mt = $total_mt;
				$this->view->total_mp = $total_mp;
				// init paginator object
				$paginator = Zend_Paginator::factory($select);
				$paginator->setItemCountPerPage($paging_config->items);
				$paginator->setCurrentPageNumber($page);
				$this->view->paginator=$paginator;
				$reportForm->populate($this->view->params);
			}else{ // export all result to a excel file
				//open file
				$filename = uniqid() . ".csv";
				 header('Content-Type: text/csv;');
                 header("Content-Disposition: attachment; filename=$filename");
                 try{
					$row = $users->getAdapter()->fetchRow($select_count);
                 }catch(Zend_Db_Statement_Exception $e){
					$exportButton->setValue('Export data');
					$reportForm->getElement('submitReportForm')->setValue("Show report");
					$this->view->form = $reportForm;
					return;
				 }
					$num_records = $row["num_rows"];
					$select_limit = $users->getAdapter()->select()
								->from(array( 'bt' => $billing_table))
								->where('bt.msisdn in (SELECT su.msisdn FROM `vmg_service_user` AS su
	LEFT JOIN `vmg_user_service` AS us ON su.service_channel_id = us.id)')
								->where('bt.date BETWEEN CAST(\'' . $startDatetime . '\' AS DATETIME) AND CAST(\'' . $endDatetime . '\' AS DATETIME)')
								->joinleft(array('su' => 'vmg_service_user'),  '(bt.msisdn = su.msisdn)', array('su.msisdn', 'su.activ_date as active_date', 'su.service_userid as service_userid'))
								->joinleft(array('us' => 'vmg_user_service'),  'su.service_channel_id = us.id', array('us.agent_id as agent_id'))
								->joinleft(array('u' => 'vmg_user'), 'u.id = us.agent_id', array('u.id', 'u.name as agent_name', 'u.managed_by'))
								->joinleft(array('um' => 'vmg_user'), 'u.managed_by = um.id', array('um.name as salesman_name'))
								->where('u.id = ?', $agent_id)
								->order('bt.date DESC')
								->where('su.activ_date in (SELECT max(activ_date) FROM vmg_service_user WHERE msisdn = bt.msisdn and bt.date > activ_date)')
								;
					echo "Nam, " . $year;
					echo "\n";
					echo "Thang, " . $month;
					echo "\n";
					$i = $num_records/1000;
				 	$tmp = array(
				 		$translator->_("Ngay"),
				 		$translator->_("Gio"),
				 		$translator->_("Di dong"),
				 		//$translator->_("Dai ly"),
				 		$translator->_("Dau so"),
						$translator->_("So luong MO"),
						$translator->_("So luong MT"),
						$translator->_("So luong MP"),
						$translator->_("Doanh thu tam tinh")
					);
					echo implode("," , $tmp) . "\n";
					for($j = 0; $j <= $i; $j++){
						$select_limit->limit(1000, $j * 1000);
						$rows = $users->getAdapter()->fetchAll($select_limit);
						foreach($rows as $row){
							$row['msisdn'] = '\'' . str_replace("+84", "0", $row['msisdn']);
							echo substr($row['date'], 8,2) . ",";
							echo substr($row['date'], 11, 2) . ",";
							echo $row['msisdn']. ",";
							//echo $row['agent_name']. ",";
							echo $row['short_code']. ",";
							echo $row['mo_count']. ",";
							echo $row['mt_count']. ",";
							echo $row['cdr_count']. ",";
							echo $row['money'];
							echo "\n";
						}
					}
					exit;
				}
			}
		// attach form to view
		$exportButton->setValue('Export data');
		$reportForm->getElement('submitReportForm')->setValue("Show report");
		$this->view->form = $reportForm;
	}

	/**
	 * Full customer development report action
	 *
	 */
	function customerreportfullAction(){
		// set title
		$this->view->title = "Customer development reports";

		// get registry value
		$registry = Zend_Registry::getInstance();
		$access_levels = $registry->get('access_level_config');
		$paging_config = $registry->get('paging_config');
		$reporting_config = $registry->get('reporting_config');
		$translator = $registry->get('Zend_Translate');

		// create form
		$reportForm = new ReportForm();
		$exportButton = $reportForm->getElement('exportReportForm');

		// remove the salesman and agent selection controls
		$reportForm->removeElement('salesmanReportForm');
		$reportForm->removeElement('agentReportForm');
		$show_flag = true;
		if($this->_request->isPost()){ // get report conditions when user post information
			$formData = $this->_request->getPost();
			if($reportForm->isValid($formData)){
				$startHour = mysql_escape_string($reportForm->getValue('startHourReportForm'));
				$startDay = mysql_escape_string($reportForm->getValue('startDayReportForm'));
				$endHour = mysql_escape_string($reportForm->getValue('endHourReportForm'));
				$endDay = mysql_escape_string($reportForm->getValue('endDayReportForm'));
				$month = mysql_escape_string($reportForm->getValue('monthReportForm'));
				$year = mysql_escape_string($reportForm->getValue('yearReportForm'));
				$page = 1;
				if($reportForm->getValue('exportReportForm') != NULL){
					$show_flag = false;
				}
			}
		}else{ // get report conditions when users naviagate through the pagination control
			$startHour = mysql_escape_string($this->_request->getParam('startHourReportForm'));
			$startDay = mysql_escape_string($this->_request->getParam('startDayReportForm'));
			$endHour = mysql_escape_string($this->_request->getParam('endHourReportForm'));
			$endDay = mysql_escape_string($this->_request->getParam('endDayReportForm'));
			$month = mysql_escape_string($this->_request->getParam('monthReportForm'));
			$year = mysql_escape_string($this->_request->getParam('yearReportForm'));
			// fetch rows to show
			$page = $this->_getParam('page',1);
		}
		$this->view->page = $page;
		$this->view->itemsPerPage = $paging_config->items;
		if(isset($year) && $year > 0){
			$month += 1 ;
			if(strlen($month) == 1) $month = '0' . $month;
			if(strlen($startDay) == 1) $startDay = '0' . $startDay;
			if(strlen($endDay) == 1) $endDay = '0' . $endDay;
			if(strlen($startHour) == 1) $startHour = '0' . $startHour;
			if(strlen($endHour) == 1) $endHour = '0' . $endHour;
			$this->view->params = array(
				'startHourReportForm' => $startHour,
				'startDayReportForm' => $startDay,
				'endHourReportForm' => $endHour,
				'endDayReportForm' => $endDay,
				'monthReportForm' => $month-1,
				'yearReportForm' => $year
			);
			$startDatetime = $year . "-" . $month . "-" . $startDay . " " . $startHour . ":00:00";
			$endDatetime = $year . "-" . $month . "-" . $endDay . " " . $endHour . ":00:00";
			$users = new Users();
			$select = $users->getAdapter()->select()
								->from(array('su' => 'vmg_service_user'))
								->where('activ_date BETWEEN CAST(\'' . $startDatetime . '\' AS DATETIME) AND CAST(\'' . $endDatetime . '\' AS DATETIME)')
								->joinLeft(array('us' => 'vmg_user_service'), 'su.service_channel_id = us.id')
								->joinLeft(array('u' => 'vmg_user'), 'u.id = us.agent_id', array('u.name as agent_name'))
								->order(array('activ_date DESC'))
								;
			$select_count = $users->getAdapter()->select()
								->from(array('su' => 'vmg_service_user'), array('count(su.msisdn) as num_rows'))
								->where('activ_date BETWEEN CAST(\'' . $startDatetime . '\' AS DATETIME) AND CAST(\'' . $endDatetime . '\' AS DATETIME)')
								->joinLeft(array('us' => 'vmg_user_service'), 'su.service_channel_id = us.id', array())
								->joinLeft(array('u' => 'vmg_user'), 'u.id = us.agent_id', array())
								;
			if($show_flag){	// show the result
				$row = $users->getAdapter()->fetchRow($select_count);
				$num_customers = $row["num_rows"];
				$this->view->num_customers = $num_customers;
				// init paginator object
				$paginator = Zend_Paginator::factory($select);
				$paginator->setItemCountPerPage($paging_config->items);
				$paginator->setCurrentPageNumber($page);
				$this->view->paginator=$paginator;
				$reportForm->populate($this->view->params);
			}else{ // export all result to a excel file
				//open file
				$filename = uniqid() . ".csv";
				 header('Content-Type: text/csv;');
                 header("Content-Disposition: attachment; filename=$filename");
					$row = $users->getAdapter()->fetchRow($select_count);
					$num_records = $row["num_rows"];
					$select_limit = $users->getAdapter()->select()
									->from(array('su' => 'vmg_service_user'))
									->where('activ_date BETWEEN CAST(\'' . $startDatetime . '\' AS DATETIME) AND CAST(\'' . $endDatetime . '\' AS DATETIME)')
									->joinLeft(array('us' => 'vmg_user_service'), 'su.service_channel_id = us.id')
									->joinLeft(array('u' => 'vmg_user'), 'u.id = us.agent_id', array('u.name as agent_name'))
									->order(array('activ_date DESC'))
									;
					echo "Nam, " . $year;
					echo "\n";
					echo "Thang, " . $month;
					echo "\n";
					$i = $num_records/1000;
				 	$tmp = array(
						$translator->_("Ngay kich hoat"),
				 		$translator->_("Gio kich hoat"),
				 		$translator->_("Thue bao"),
				 		$translator->_("Dai ly"),
						$translator->_("Hang SX"),
						$translator->_("Loai DT"),
						$translator->_("Chu thue bao"),
						$translator->_("Thanh pho"),
						$translator->_("Dia chi"),
						$translator->_("Email")
					);
					echo implode("," , $tmp) . "\n";
					for($j = 0; $j <= $i; $j++){
						$select_limit->limit(1000, $j * 1000);
						$rows = $users->getAdapter()->fetchAll($select_limit);

						foreach($rows as $row){
							echo substr($row['activ_date'], 8,2) . ",";
							echo substr($row['activ_date'], 11, 2) . ",";
							$row['msisdn'] = str_replace("+84", "'0", $row['msisdn']);
							echo $row['msisdn'] . ",";
							echo str_replace(",", "-",$row['agent_name']) . ",";
							echo str_replace(",", "-",$row['device_brand']) . ",";
							echo $row['device_model'] . ",";
							echo $row['fullname'] . ",";
							echo str_replace(",", "-",$row['city']) . ",";
							echo str_replace(",", "-",$row['address']) . ",";
							echo $row['email'];
							echo  "\n";
						}
					}
					exit;
				}
			}
		// attach form to view
		$exportButton->setValue('Export data');
		$reportForm->getElement('submitReportForm')->setValue("Show report");
		$this->view->form = $reportForm;

	}
}